﻿Imports System.Data.SqlClient

Public Class clsSalesQuotationData

    Public Function SalesQuotationData_Open(ByVal sSalesQuotNum As String, ByVal sCustName As String, ByVal sContPerName As String _
                                            , ByVal sConnectionString As String) As DataSet

        Dim oDataSet As DataSet
        Dim oDataAdapter As SqlDataAdapter
        Dim oDataTable As DataTable
        Dim sQuery As String = String.Empty
        Dim sFuncName As String = String.Empty
        'Reading the Header Values Based on Condition
        Try
            sFuncName = "SalesQuotationData_Open()"
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            If sSalesQuotNum.ToString.Trim() IsNot Nothing AndAlso sSalesQuotNum.ToString.Trim() Then
                sSalesQuotNum = "%"
            Else
                sSalesQuotNum = sSalesQuotNum & "%"
            End If
            If sCustName.ToString.Trim() IsNot Nothing AndAlso sCustName.ToString.Trim() Then
                sCustName = "%"
            Else
                sCustName = sCustName & "%"
            End If
            If sContPerName.ToString.Trim() IsNot Nothing AndAlso sContPerName.ToString.Trim() Then
                sContPerName = "%"
            Else
                sContPerName = sContPerName & "%"
            End If

            oDataSet = New DataSet

            sQuery = "EXEC AE_SPES01_SalesQuotationData '" & sSalesQuotNum & "','" & sCustName & "','" & sContPerName & "'"

 

            oDataAdapter = New SqlDataAdapter(sQuery, sConnectionString)
            oDataTable = New DataTable
            oDataAdapter.Fill(oDataTable)
            oDataSet.Tables.Add(oDataTable)
            If oDataSet.Tables(0).Rows.Count > 0 Then

                ''Get a DocEntry for Read the Lines Level Values Based on Respective DocEntry

                'sQuery = ""
                'sQuery = "declare  @diststring as varchar(max)"
                'sQuery += vbCrLf + "select @diststring=coalesce(@diststring+ ''', ''','')+convert(varchar,docentry)from OQUT Where DocStatus='O'"
                'sQuery += vbCrLf + "select  ''''+@diststring+ '''' as DocEntry "
                'oDataTable = New DataTable
                'oDataAdapter = New SqlDataAdapter(sQuery, sConnectionString)
                'oDataAdapter.Fill(oDataTable)


                ''To Get Get a Line Level Data's 

                'sQuery = ""
                'sQuery += vbCrLf + "SELECT T0.[DocEntry], T0.[LineNum], T0.[WhsCode], T0.[ItemCode], T0.[U_EASIRcpt], T0.[Price], T0.[Quantity], " & _
                '    " T0.[LineTotal], T0.[Project] FROM QUT1 T0 where T0.[DocEntry] in (" & oDataTable.Rows(0)("DocEntry").ToString & ")  "

                'oDataAdapter = New SqlDataAdapter(sQuery, sConnectionString)
                'oDataTable = New DataTable
                'oDataAdapter.Fill(oDataTable)
                'oDataSet.Tables.Add(oDataTable)

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed with SUCCESS", sFuncName)
                Return oDataSet
            Else
                Return Nothing
            End If
        Catch ex As Exception

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed with ERROR", sFuncName)
            WriteToLogFile(ex.Message, sFuncName)
            Return Nothing
        End Try

    End Function

    Public Function SalesQuotationData_Closed(ByVal sConnectionString As String) As DataSet

        Dim oDataSet As DataSet
        Dim oDataAdapter As SqlDataAdapter
        Dim oDataTable As DataTable
        Dim sQuery As String = String.Empty
        Dim sFuncName As String = String.Empty
        'Reading the Header Values Based on Condition
        Try
            sFuncName = "SalesQuotationData_Closed()"
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            oDataSet = New DataSet

            sQuery = " SELECT T0.[DocEntry],T0.[DocNum] [Quotation Number], T0.[CardCode][Customer Account No] " & _
                ", T0.[CardName][Customer Full Name],ISNULL ( T1.FirstName,'')+' '+ISNULL (T1.MiddleName,'')+' '+ISNULL (T1.LastName,'') [Contact Name] " & _
                ", T0.[Address][Billing Address], T1.[Tel1][Telephone], T1.[E_MailL][Email], T0.[DocNum][Event Order Number], T2.[SlpName][Person-In-Charge] " & _
                ", T0.[U_DistDate][Distribution Date], T0.[DocDueDate][Event Date], T0.[U_EventTimeStart][Event Start Time], T0.[U_EventTimeEnd][Event End Time] " & _
                ", T0.[U_FoodTimeStart][Food Time Start], T0.[U_FoodTimeEnd] [Food Time End], T0.[U_BvgTimeStart][Bvg Time Start] " & _
                " , T0.[U_BvgTimeEnd][Bvg Time End], T0.[U_Venue][Event Venue], T0.[U_Function][Event Function] " & _
                " , T0.[U_Setup][Event Setup], T0.[U_GTD_Attd][GTD Attendance], T0.[U_EXP_Attd][EXP Attendance] " & _
                " , T0.[U_AudioVisual][Audio Visual], T0.[U_BanquetOp][Banquet Operation], T0.[U_Linens][Linens], T0.[U_Signage][Signage], T0.[U_Finance][Finance Instructions]" & _
                " ,T0.[U_ReqDownPaymt][ReqDownPayment], T0.[Project][Revenue Center Name], T0.[DocTotal][Gross Total] " & _
                " , T0.[U_PaidToDate][Paid To Date],CASE when T0.[DocStatus] ='O' then 'Open' when T0.DocStatus ='C' then 'Closed' End [Sales Order Status] " & _
                " FROM OQUT T0  LEFT OUTER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]  LEFT OUTER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] Where T0.[DocStatus]='C'"


            oDataAdapter = New SqlDataAdapter(sQuery, sConnectionString)
            oDataTable = New DataTable
            oDataAdapter.Fill(oDataTable)
            oDataSet.Tables.Add(oDataTable)
            If oDataSet.Tables(0).Rows.Count > 0 Then

                'Get a DocEntry for Read the Lines Level Values Based on Respective DocEntry

                ''sQuery = ""
                ''sQuery = "declare  @diststring as varchar(max)"
                ''sQuery += vbCrLf + "select @diststring=coalesce(@diststring+ ''', ''','')+convert(varchar,docentry)from OQUT Where DocStatus='C'"
                ''sQuery += vbCrLf + "select  ''''+@diststring+ '''' as DocEntry "
                ''oDataTable = New DataTable
                ''oDataAdapter = New SqlDataAdapter(sQuery, sConnectionString)
                ''oDataAdapter.Fill(oDataTable)


                ' ''To Get Get a Line Level Data's 

                ''sQuery = ""
                ''sQuery += vbCrLf + "SELECT T0.[DocEntry], T0.[LineNum], T0.[WhsCode], T0.[ItemCode], T0.[U_EASIRcpt], T0.[Price], T0.[Quantity], " & _
                ''    " T0.[LineTotal], T0.[Project] FROM QUT1 T0 where T0.[DocEntry] in (" & oDataTable.Rows(0)("DocEntry").ToString & ")  "

                ''oDataAdapter = New SqlDataAdapter(sQuery, sConnectionString)
                ''oDataTable = New DataTable
                ''oDataAdapter.Fill(oDataTable)
                ''oDataSet.Tables.Add(oDataTable)

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed with SUCCESS", sFuncName)
                Return oDataSet
            Else

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed with SUCCESS", sFuncName)
                Return Nothing
            End If
        Catch ex As Exception
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed with ERROR", sFuncName)
            WriteToLogFile(ex.Message, sFuncName)
            Return Nothing
        End Try

    End Function

End Class
